﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using zichan.Entity;
using zichan.DAL;
using MvcPaging;
using zichan.Entity.View;
using System.Data.SqlClient;

namespace zichan.BLL
{
    public class StaffService
    {
        public static string StaffAdd(Staff model)
        {
            using (ZichanDBContext db = new ZichanDBContext())
            {
                Staff staff = db.Staffs.FirstOrDefault(o => o.Id == model.Id);
                if (staff == null)
                    db.Staffs.Add(model);
                else
                {
                    staff.Name = model.Name;
                    staff.DepartmentName = model.DepartmentName;
                }
                db.SaveChanges();
                return string.Empty;
            }
        }

        public static Staff Get(int id)
        {
            using (ZichanDBContext db = new ZichanDBContext())
            {
                return db.Staffs.FirstOrDefault(o => o.Id == id);
            }
        }

        public static int GetByName(string name)
        {
            using (ZichanDBContext db = new ZichanDBContext())
            {
              int id=db.Staffs.Where(o => o.Name == name).Select(o => o.Id).FirstOrDefault();
                return id;
            }
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="name"></param>
        /// <param name="email"></param>
        /// <param name="pageSize"></param>
        /// <param name="pageIndex"></param>
        /// <returns></returns>
        public static IPagedList<StaffView> StaffList(string name, string departmentName, int? roleId, int pageSize, int pageIndex)
        {
            using (ZichanDBContext db = new ZichanDBContext())
            {
                var w = (from s in db.Staffs select new { s.Id, s.Name, s.DepartmentName });
                if (!string.IsNullOrEmpty(name))
                    w = w.Where(o => o.Name.Contains(name));
                if (!string.IsNullOrEmpty(departmentName))
                    w = w.Where(o => o.DepartmentName.Contains(departmentName));
                if (roleId.HasValue)
                {
                    List<int> ids = db.StaffRoles.Where(o => o.RoleId == roleId.Value).Select(o => o.StaffId).ToList();
                    w = w.Where(o => ids.Contains(o.Id));
                }


                var staffs = w.OrderBy(o => o.Id).Skip(pageSize * (pageIndex - 1)).Take(pageSize).ToList();//

                List<int> sids = staffs.Select(o => o.Id).ToList();
                var srids = db.StaffRoles.Where(o => sids.Contains(o.StaffId)).Select(o => new { o.StaffId, o.RoleId }).ToList();
                List<int> rids = srids.Select(o => o.RoleId).ToList();
                var roles = db.Roles.Where(o => rids.Contains(o.Id)).Select(o => new { o.Id, o.Name }).ToList();
                List<StaffView> svs = new List<StaffView>();
                foreach (var s in staffs)
                {
                    string RoleName = "";
                    var srid = srids.Where(o => o.StaffId == s.Id).Select(o => o.RoleId).ToList();
                    if (srid != null)
                    {
                        var rs = roles.Where(o => srid.Contains(o.Id)).Select(o => o.Name).ToList();
                        RoleName = string.Join(",", rs);
                    }
                    svs.Add(new StaffView { Id = s.Id, Name = s.Name, DepartmentName = s.DepartmentName, RoleNames = RoleName });
                }

                return svs.ToPagedList(pageIndex - 1, pageSize, w.Count());
            }
        }

        public static List<Staff> StaffAll(int? status)
        {
            using (ZichanDBContext db = new ZichanDBContext())
            {
                if (status.HasValue)
                    return db.Staffs.Where(o => o.Status == status.Value).ToList();
                return db.Staffs.ToList();
            }
        }

        public static void InsUptStaff(List<Staff> adds, List<Staff> edits)
        {
            using (System.Transactions.TransactionScope scope = new System.Transactions.TransactionScope())
            {
                using (ZichanDBContext db = new ZichanDBContext())
                {
                    foreach (Staff add in adds)
                        db.Staffs.Add(add);

                    foreach (Staff edit in edits)
                        db.Entry<Staff>(edit).State = System.Data.Entity.EntityState.Modified;

                    db.SaveChanges();
                }
                scope.Complete();
            }
        }

        /// <summary>
        /// 获取职员对应角色
        /// </summary>
        /// <param name="sid"></param>
        /// <returns></returns>
        public static List<StaffRoleView> StaffRoleList(int sid)
        {
            using (ZichanDBContext db = new ZichanDBContext())
            {
                List<StaffRoleView> list = (from sr in db.StaffRoles
                                            join r in db.Roles on sr.RoleId equals r.Id
                                            where sr.StaffId == sid
                                            select new StaffRoleView { Id = sr.Id, RoleName = r.Name, StaffId = sr.StaffId, RoleId = sr.RoleId }).ToList();
                return list;
            }
        }


        public static string StaffRoleAdd(int staffId, int roleId)
        {
            using (ZichanDBContext db = new ZichanDBContext())
            {
                Staff staff = db.Staffs.FirstOrDefault(o => o.Id == staffId);
                if (staff == null)
                    return "职员不存在";
                Role role = db.Roles.FirstOrDefault(o => o.Id == roleId);
                if (role == null)
                    return "角色不存在";
                if (db.StaffRoles.Count(o => o.RoleId == roleId && o.StaffId == staffId) > 0)
                    return "职员角色已存在";

                StaffRole sr = new StaffRole();
                sr.StaffId = staffId;
                sr.RoleId = roleId;
                db.StaffRoles.Add(sr);
                db.SaveChanges();

                return string.Empty;
            }
        }
        public static int StaffRoleBatchsAdd(string pIds, int roleId, string connectstring)
        {
            try {
                SqlConnection sqlcon = new SqlConnection();
                sqlcon.ConnectionString = connectstring;
                sqlcon.Open();
                using (SqlCommand com = new SqlCommand("Pro_BacthsRole", sqlcon))
                {
                    com.CommandType = System.Data.CommandType.StoredProcedure;
                    SqlParameter param = new SqlParameter();
                    param.ParameterName = "@ids";
                    param.SqlDbType = System.Data.SqlDbType.NVarChar;
                    param.Value = pIds;
                    com.Parameters.Add(param);
                    param = new SqlParameter();
                    param.ParameterName = "@rId";
                    param.SqlDbType = System.Data.SqlDbType.Int;
                    param.Value = roleId;
                    com.Parameters.Add(param);
                    param = new SqlParameter();
                    param.ParameterName = "@splitchar";
                    param.SqlDbType = System.Data.SqlDbType.Char;
                    param.Value = ',';
                    com.Parameters.Add(param);
                    int i = com.ExecuteNonQuery();
                    return i;
                }
            }
            catch(Exception ex)
            {
                return -1;
            }
        }
        public static string StaffRoleRemove(int id)
        {
            using (ZichanDBContext db = new ZichanDBContext())
            {
                StaffRole sr = db.StaffRoles.FirstOrDefault(o => o.Id == id);
                if (sr == null)
                    return "职员角色不存在";

                db.StaffRoles.Remove(sr);
                db.SaveChanges();
                return string.Empty;
            }
        }
        public static List<StatisticsModel.TreeModel> TreeStaff()
        {
            using (ZichanDBContext db = new ZichanDBContext())
            {

                List<StatisticsModel.TreeModel> m = new List<StatisticsModel.TreeModel>();
                

                m = (from p in db.Staffs
                     group p by p.DepartmentName into S
                     select new StatisticsModel.TreeModel
                     {
                         id = db.Departments.Where(o => o.DepartmentName == S.Key).Select(o => o.Id).FirstOrDefault(),
                         pId = 0,
                         name = S.Key,
                         children = db.Staffs.Where(o => o.DepartmentName == S.Key && o.Status == 1).Select(o => new StatisticsModel.TreeChildModel { id = o.Id, pId = 1, name = o.Name }).ToList<StatisticsModel.TreeChildModel>()
                     }).ToList<StatisticsModel.TreeModel>();
                return m;
            }
        }
    }
}
